﻿<%@ WebHandler Language="C#" Class="MfgorderPrepareMaintPageHandler" %>

/* Description:订单点检信息维护后端接口
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan 
 * create Date:2024.11.19
 */

using System;
using System.Web;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BusinessRule.DTO;
using DatabaseAccess.DBUtility;
using System.Web.SessionState;
using Newtonsoft.Json;
using System.Collections;
using System.IO;
using System.Configuration;

public class MfgorderPrepareMaintPageHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo = new CurrentUserInfo();
    string exportPath = ConfigurationManager.AppSettings["ExportPath"]; //导出路径
    string fileBasePath = ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "getUserInfo":
                    getUserInfo(context);
                    break;
                case "getProductfamily":
                    getProductfamily(context);
                    break;
                case "getData":
                    getData(context);
                    break;
                case "setAllSet":
                    setAllSet(context);//一键齐套
                    break;
                case "sendStart":
                    sendStart(context);//下发
                    break;
                case "recycle":
                    recycle(context);//回收
                    break;
                case "updateData":
                    updateData(context);//修改
                    break;
                case "exportExcel":
                    exportExcel(context);//导出
                    break;
                case "setAllSetAndSendStart":
                    setAllSetAndSendStart(context);//一键齐套并下发
                    break;
                case "MfgOrderCheck"://订单点检
                    MfgOrderCheck(context);
                    break;
                case "ImportMfgCheckData"://导入
                    ImportMfgCheckData(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(result));
        }
    }

    //生成查询sql
    string getQuerySql(HttpContext context)
    {
        string sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,mfg.qty,f.factoryname,pb2.productname,p.productrevision,mfg.productfamilyname,
       mfg.receivedate,mfg.plannedstartdate,mfg.plannedcompletiondate,
       --DECODE(mfg.iscompauxmaterial,1,'是','否') iscompauxmaterial,
       --DECODE(mfg.iscompproduct,1,'是','否') iscompproduct,
       --DECODE(mfg.iscomptool,1,'是','否') iscomptool,
       CASE WHEN mfg.iscompauxmaterial = '2' THEN '预齐套'
            WHEN mfg.iscompauxmaterial = '1' THEN '是'
            ELSE '否' 
       END iscompauxmaterial,
       CASE WHEN mfg.iscompproduct = '2' THEN '预齐套'
            WHEN mfg.iscompproduct = '1' THEN '是'
            ELSE '否' 
       END iscompproduct,
       CASE WHEN mfg.iscomptool = '2' THEN '预齐套'
            WHEN mfg.iscomptool = '1' THEN '是'
            ELSE '否' 
       END iscomptool,
       DECODE(mfg.sendstart,1,'是','否') issendstart,e.fullname,mfg.sendstartdate,NVL(wfb.revofrcdid, p.workflowid) workflowid,mfg.estallsetdate
FROM mfgorder mfg
LEFT JOIN factory f ON f.factoryid = mfg.factoryid
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN productbase pb2 ON pb2.productbaseid = p.productbaseid
LEFT JOIN employee e ON e.employeeid = mfg.sendstartemp
LEFT JOIN container c ON c.mfgorderid = mfg.mfgorderid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = p.workflowbaseid
WHERE 1=1 ";
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND mfg.factoryid = '{0}'", context.Request["factoryid"].ToString());
        }

        if (context.Request["productfamilyname"] != null && context.Request["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND mfg.productfamilyname LIKE '%{0}%'", context.Request["productfamilyname"].ToString());
        }

        if (context.Request["mfgordername"] != null && context.Request["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername LIKE '%{0}%'", context.Request["mfgordername"].ToString());
        }

        //计划完成时间
        if (context.Request["plannedcompletiondateStart"] != null && context.Request["plannedcompletiondateStart"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(mfg.plannedcompletiondate) >= TO_DATE('{0}','yyyy-mm-dd')", context.Request["plannedcompletiondateStart"].ToString());
        }

        if (context.Request["plannedcompletiondateEnd"] != null && context.Request["plannedcompletiondateEnd"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(mfg.plannedcompletiondate) <= TO_DATE('{0}','yyyy-mm-dd')", context.Request["plannedcompletiondateEnd"].ToString());
        }

        //创建时间
        if (context.Request["receiveDateStart"] != null && context.Request["receiveDateStart"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(mfg.receivedate) >= TO_DATE('{0}','yyyy-mm-dd')", context.Request["receiveDateStart"].ToString());
        }

        if (context.Request["receiveDateEnd"] != null && context.Request["receiveDateEnd"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(mfg.receivedate) <= TO_DATE('{0}','yyyy-mm-dd')", context.Request["receiveDateEnd"].ToString());
        }

        //下发时间
        if (context.Request["sendStartBegin"] != null && context.Request["sendStartBegin"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(mfg.sendstartdate) >= TO_DATE('{0}','yyyy-mm-dd')", context.Request["sendStartBegin"].ToString());
        }

        if (context.Request["sendStartEnd"] != null && context.Request["sendStartEnd"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(mfg.sendstartdate) <= TO_DATE('{0}','yyyy-mm-dd')", context.Request["sendStartEnd"].ToString());
        }

        //件号
        if (context.Request["productname"] != null && context.Request["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb2.productname LIKE '%{0}%'", context.Request["productname"].ToString());
        }

        //下发状态
        if (context.Request["status"] != null)
        {
            string status = context.Request["status"].ToString();
            if (status == "已下发")
            {
                sql += " AND NVL(mfg.sendstart,0) = 1";
            }
            if (status == "未下发")
            {
                sql += " AND NVL(mfg.sendstart,0) = 0";
            }
        }

        //是否齐套
        if (context.Request["isAllSet"] != null)
        {
            string isAllSet = context.Request["isAllSet"].ToString();
            if (isAllSet == "是")
            {
                sql += " AND (NVL(mfg.iscompauxmaterial,0) = 1 AND NVL(mfg.iscompproduct,0) = 1 AND NVL(mfg.iscomptool,0) = 1)";
            }
            if (isAllSet == "否")
            {
                sql += " AND NOT (NVL(mfg.iscompauxmaterial,0) = 1 AND NVL(mfg.iscompproduct,0) = 1 AND NVL(mfg.iscomptool,0) = 1)";
            }
        }

        //辅材是否齐套
        if (context.Request["isAuxSet"] != null)
        {
            string isAuxSet = context.Request["isAuxSet"].ToString();
            if (isAuxSet == "是")
            {
                sql += " AND NVL(mfg.iscompauxmaterial,0) = 1";
            }
            if (isAuxSet == "否")
            {
                sql += " AND NVL(mfg.iscompauxmaterial,0) = 0";
            }
            if (isAuxSet == "预齐套")
            {
                sql += " AND NVL(mfg.iscompauxmaterial,0) = 2";
            }
        }

        //物料是否齐套
        if (context.Request["isProductSet"] != null)
        {
            string isProductSet = context.Request["isProductSet"].ToString();
            if (isProductSet == "是")
            {
                sql += " AND NVL(mfg.iscompproduct,0) = 1";
            }
            if (isProductSet == "否")
            {
                sql += " AND NVL(mfg.iscompproduct,0) = 0";
            }
            if (isProductSet == "预齐套")
            {
                sql += " AND NVL(mfg.iscompproduct,0) = 2";
            }
        }

        //工装是否齐套
        if (context.Request["isToolSet"] != null)
        {
            string isToolSet = context.Request["isToolSet"].ToString();
            if (isToolSet == "是")
            {
                sql += " AND NVL(mfg.iscomptool,0) = 1";
            }
            if (isToolSet == "否")
            {
                sql += " AND NVL(mfg.iscomptool,0) = 0";
            }
            if (isToolSet == "预齐套")
            {
                sql += " AND NVL(mfg.iscomptool,0) = 2";
            }
        }
        return sql;
    }

    void getUserInfo(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", ReturnData = m_UserInfo }));
        }
    }

    void getProductfamily(HttpContext context)
    {
        string sql = @"SELECT pf.productfamilyid,pf.productfamilyname FROM productfamily pf";
        DataTable result = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in result.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = result }));
    }

    void getData(HttpContext context)
    {
        string sql = getQuerySql(context);
        uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageNumber"].ToString());//页码
        paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
        paraPage.strSQL = sql;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        foreach (DataColumn col in result.DBTable.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    //一键齐套
    void setAllSet(HttpContext context)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string mfgorderid = dr["mfgorderid"].ToString();
            string updateSql = @"UPDATE mfgorder mfg
                           SET mfg.iscompauxmaterial = 1,
                               mfg.iscompproduct = 1,
                               mfg.iscomptool = 1
                           WHERE mfg.mfgorderid = '{0}'";
            updateSql = string.Format(updateSql, mfgorderid);
            sqlList.Add(updateSql);
        }
        Boolean isSuccess = DatabaseHelper.ExecuteSqlListTran(sqlList);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = isSuccess, Message = "", DBTable = null }));
    }

    //下发
    void sendStart(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "下发成功" };
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        string isSendSql = @"SELECT 1 FROM mfgorder m WHERE m.mfgorderid = '{0}' AND m.sendstart = 1";
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            if (DatabaseHelper.GetSingle(string.Format(isSendSql, dr["mfgorderid"].ToString())) != null)
            {
                result.Result = false; result.Message = "订单已下发，请勿重复下发";
                break;
            }
            string mfgorderid = dr["mfgorderid"].ToString();
            string updateSql = @"UPDATE mfgorder mfg
                           SET mfg.sendstart = 1,
                               mfg.sendstartdate = sysdate,
                               mfg.sendstartemp = '{1}'
                           WHERE mfg.mfgorderid = '{0}'";
            updateSql = string.Format(updateSql, mfgorderid, m_UserInfo.EmployeeID);
            sqlList.Add(updateSql);
        }
        if (result.Result)
        {
            DatabaseHelper.ExecuteSqlListTran(sqlList);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    //回收
    void recycle(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string mfgorderid = dr["mfgorderid"].ToString();
            string updateSql = @"UPDATE mfgorder mfg SET mfg.sendstart = 0 WHERE mfg.mfgorderid = '{0}'";
            updateSql = string.Format(updateSql, mfgorderid);
            sqlList.Add(updateSql);
        }
        Boolean isSuccess = DatabaseHelper.ExecuteSqlListTran(sqlList);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = isSuccess, Message = "", DBTable = null }));
    }

    //修改
    void updateData(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "修改成功" };
        string isSendSql = @"SELECT 1 FROM mfgorder m WHERE m.mfgorderid = '{0}' AND m.sendstart = 1";
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            if (DatabaseHelper.GetSingle(string.Format(isSendSql, dr["mfgorderid"].ToString())) != null)
            {
                result.Result = false; result.Message = "订单已下发，无法修改";
                break;
            }
            string iscompauxmaterial = dr["iscompauxmaterial"].ToString();
            string iscompproduct = dr["iscompproduct"].ToString();
            string iscomptool = dr["iscomptool"].ToString();
            string estallsetdate = dr["estallsetdate"].ToString();

            string mfgorderid = dr["mfgorderid"].ToString();
            string updateSql = @"UPDATE mfgorder mfg
                           SET mfg.iscompauxmaterial = '{1}',
                               mfg.iscompproduct = '{2}',
                               mfg.iscomptool = '{3}',
                               mfg.estallsetdate=to_date('{4}','yyyy-MM-dd hh24:mi:ss')
                           WHERE mfg.mfgorderid = '{0}'";
            updateSql = string.Format(updateSql, mfgorderid, iscompauxmaterial, iscompproduct, iscomptool, estallsetdate);
            sqlList.Add(updateSql);
        }
        if (result.Result)
        {
            DatabaseHelper.ExecuteSqlListTran(sqlList);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    //导出
    void exportExcel(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "导出成功" };
        string sql = getQuerySql(context);

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        if (dt.Rows.Count == 0)
        {
            result = new uMESProcessResult() { Result = false, Message = "没有数据不能导出" };
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        else
        {
            dt.Columns.Remove("mfgorderid");
            dt.Columns.Remove("productrevision");
            dt.Columns.Remove("workflowid");
            dt.Columns["mfgordername"].ColumnName = "订单号";
            dt.Columns["qty"].ColumnName = "订单数量";
            dt.Columns["factoryname"].ColumnName = "主制分厂";
            dt.Columns["productname"].ColumnName = "零件号";
            dt.Columns["productfamilyname"].ColumnName = "型别";
            dt.Columns["receivedate"].ColumnName = "创建时间";
            dt.Columns["plannedstartdate"].ColumnName = "计划开始时间";
            dt.Columns["plannedcompletiondate"].ColumnName = "计划完成时间";
            dt.Columns["issendstart"].ColumnName = "是否下发";
            dt.Columns["sendstartdate"].ColumnName = "下发时间";
            dt.Columns["iscompauxmaterial"].ColumnName = "辅材是否齐套";
            dt.Columns["iscompproduct"].ColumnName = "物料是否齐套";
            dt.Columns["iscomptool"].ColumnName = "工装是否齐套";
            dt.Columns["fullname"].ColumnName = "下发人";
            dt.Columns["estallsetdate"].ColumnName = "预齐套时间";

            string fileName = "订单点检信息维护导出" + DateTime.Now.ToString("yyyyMMddHHmmddssffff") + ".xls";
            string strPath = context.Request.MapPath(exportPath + "CheckItemTem/");
            if (!Directory.Exists(strPath)) //如果没有目录，创建
            {
                Directory.CreateDirectory(strPath);
            }
            strPath += fileName;
            byte[] ms = uLMS_CommonFunction.DataTableExportToExcel(dt, null, null);
            uLMS_CommonFunction.ParseBytesToFile(ms, strPath);
            string url = "http://" + context.Request.Url.Host + context.Request.ApplicationPath + exportPath.Trim('~').Replace("\\", "/") + "CheckItemTem/" + fileName;
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("filename", fileName);
            dic.Add("url", url);
            result.ReturnData = dic;
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    //一键齐套并下发
    void setAllSetAndSendStart(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "下发成功" };
        string isSendSql = @"SELECT 1 FROM mfgorder m WHERE m.mfgorderid = '{0}' AND m.sendstart = 1";
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            if (DatabaseHelper.GetSingle(string.Format(isSendSql, dr["mfgorderid"].ToString())) != null)
            {
                result.Result = false; result.Message = "订单已下发，请勿重复下发";
                break;
            }
            string mfgorderid = dr["mfgorderid"].ToString();
            string updateSql = @"UPDATE mfgorder mfg
                           SET mfg.iscompauxmaterial = 1,
                               mfg.iscompproduct = 1,
                               mfg.iscomptool = 1,
                               mfg.sendstart = 1,
                               mfg.sendstartdate = sysdate,
                               mfg.sendstartemp = '{1}'
                           WHERE mfg.mfgorderid = '{0}'";
            updateSql = string.Format(updateSql, mfgorderid, m_UserInfo.EmployeeID);
            sqlList.Add(updateSql);
        }
        if (result.Result)
        {
            DatabaseHelper.ExecuteSqlListTran(sqlList);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 订单点检
    /// </summary>
    /// <param name="context"></param>
    void MfgOrderCheck(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "点检成功" };
        string isSendSql = @"SELECT 1 FROM mfgorder m WHERE m.mfgorderid = '{0}' AND m.sendstart = 1";
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["data"].ToString());
        ArrayList sqlList = new ArrayList();
        string updateSql = @"UPDATE mfgorder mfg SET mfg.iscompauxmaterial = '{1}', mfg.iscompproduct = '{2}', mfg.iscomptool = '{3}'
                             WHERE mfg.mfgorderid = '{0}'";
        foreach (DataRow row in dtPostData.Rows)
        {
            if (DatabaseHelper.GetSingle(string.Format(isSendSql, row["mfgorderid"].ToString())) != null)
            {
                result.Result = false; result.Message = "订单已下发，无法点检";
                break;
            }
            string iscompproduct = CheckMfgProductBom(row["workflowid"].ToString());
            string iscomptool = CheckMfgProductTool(row["workflowid"].ToString());
            string iscompauxmaterial = CheckMfgProductAux(row["workflowid"].ToString());
            sqlList.Add(string.Format(updateSql, row["mfgorderid"], iscompauxmaterial, iscompproduct, iscomptool));
        }
        if (result.Result)
        {
            DatabaseHelper.ExecuteSqlListTran(sqlList);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 物料是否齐套
    /// </summary>
    /// <param name="workflowid"></param>
    /// <returns></returns>
    string CheckMfgProductBom(string workflowid)
    {
        //ERP缺料报表对应数量《所需数量，则不齐套   
        var strSQL = @"SELECT ba.BOMNAME,b.BOMREVISION,pb.PRODUCTNAME,p.DESCRIPTION,pm.QTYREQUIRED 
            FROM BOM b
            LEFT JOIN BOMBASE ba ON ba.BOMBASEID =b.BOMBASEID 
            LEFT JOIN productmateriallistitem pm ON pm.BOMID =b.BOMID 
            LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid
            LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid)
            LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
            ---LEFT JOIN xxxx x on       
            WHERE b.WORKFLOWID ='{0}' --and x.QTY<pm.QTYREQUIRED ";
        var dt = DatabaseHelper.Query_ReturnDatTable(string.Format(strSQL, workflowid));
        return dt.Rows.Count > 0 ? "0" : "1";
    }
    /// <summary>
    /// 工装工具是否齐套
    /// </summary>
    /// <param name="workflowid"></param>
    /// <returns></returns>
    string CheckMfgProductTool(string workflowid)
    {
        var strSQL = @"select * from (
	                    select * from (
		                     select tools,sum(qty) qty from (
		                      select t.qty,t.tools
	                          from workflowsteptools t
	                          left join workflowstep ws on ws.workflowstepid =t.workflowstepid 
	                          where ws.workflowid ='{0}'
	                          union
	                          select t.qty, t.tools 
	                          from stepinfotools t
	                          left join stepinfo sio on sio.stepinfoid = t.stepinfoid
	                          left join workflowstep ws on ws.workflowstepid=sio.workflowstepid 
	                          where ws.workflowid ='{0}'
	                          ) group by tools
                         ) t
                    left join  (
	                      select cuttoolname toolname,description,cuttoolid toolid,drawingno,'刀具' tooltype
                          from  cuttool
                          union all
                          select measuretoolname toolname,description,measuretoolid toolid,drawingno,'量具' tooltype 
                          from  measuretool
                          union all
                          select specialtoolname toolname,description,specialtoolid toolid,drawingno,'夹模具' tooltype
                          from  specialtool
                       ) ct on ct.toolid = t.tools
                        ) t
                    left join (
                         select sum(tg.qty) menge,tg.toolname 
                         from tool_stock_info tg 
                         group by tg.toolname
                    ) tg on tg.toolname=t.toolname where tg.menge<t.qty";
        var dt = DatabaseHelper.Query_ReturnDatTable(string.Format(strSQL, workflowid));
        return dt.Rows.Count > 0 ? "0" : "1";
    }
    /// <summary>
    /// 辅材是否齐套
    /// </summary>
    /// <param name="workflowid"></param>
    /// <returns></returns>
    string CheckMfgProductAux(string workflowid)
    {
        //BPM线边库组成中间表数量《所需数量，则不齐套
        var strSQL = @"
        select * from ( 
                select auxmaterialname,sum(qty) qty from (
                        select am.auxmaterialname,sft.qty
                        from workflowstep wfs                       
                        inner join workflowsteptools sft on sft.workflowstepid=wfs.workflowstepid
                        inner join auxmaterial am on am.auxmaterialid=sft.tools
                        where wfs.workflowid ='{0}'
                        union all 
                        select am.auxmaterialname,st.qty
                        from stepinfo si
                        inner join stepinfotools st on st.stepinfoid=si.stepinfoid
                        inner join auxmaterial am on am.auxmaterialid=st.tools
                        left join workflowstep ws on ws.workflowstepid=si.workflowstepid
                        where ws.workflowid ='{0}'
                        ) group by auxmaterialname
                    ) am 
                    left join(
                         select sum(tg.qty) menge,tg.auxmaterialname 
                         from auxmaterial_stock_info tg 
                         group by tg.auxmaterialname
                    ) tg on tg.auxmaterialname=am.auxmaterialname
                where tg.menge<am.qty";
        var dt = DatabaseHelper.Query_ReturnDatTable(string.Format(strSQL, workflowid));
        return dt.Rows.Count > 0 ? "0" : "1";
    }
    //导入
    void ImportMfgCheckData(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            if (!System.IO.Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = System.IO.Path.GetExtension(file.FileName);
            string fileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除
            NPOI.SS.UserModel.IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            DataTable dt = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            uLMS_CommonFunction.DeleteFile(fpath);
            uMESProcessResult result = SaveMfgCheckData(dt);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            uLMS_CommonFunction.DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }

    uMESProcessResult SaveMfgCheckData(DataTable dt)
    {
        uMESProcessResult result = new uMESProcessResult();
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dt.Rows)
        {
            string iscompauxmaterial = dr["辅材是否齐套"].ToString().Equals("是") ? "1" : "0";
            string iscompproduct = dr["物料是否齐套"].ToString().Equals("是") ? "1" : "0";
            string iscomptool = dr["工装是否齐套"].ToString().Equals("是") ? "1" : "0";
            string estallsetdate = dr["预齐套时间"].ToString();
            string mfgordername = dr["订单号"].ToString();

            string updateSql = @"UPDATE mfgorder mfg
                           SET mfg.iscompauxmaterial = '{1}',
                               mfg.iscompproduct = '{2}',
                               mfg.iscomptool = '{3}',
                               mfg.estallsetdate=to_date('{4}','yyyy-MM-dd hh24:mi:ss')
                           WHERE mfg.mfgordername = '{0}'";
            updateSql = string.Format(updateSql, mfgordername, iscompauxmaterial, iscompproduct, iscomptool, estallsetdate);
            sqlList.Add(updateSql);
        }
        Boolean isSuccess = DatabaseHelper.ExecuteSqlListTran(sqlList);
        result.Result = isSuccess;
        result.Message = result.Result ? "导入成功" : "导入失败";
        return result;
    }

}